﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace ImportExcel
{
    public partial class MainForm : Form
    {
        private DataTable _data;

        private List<int> _updateIdList = new List<int>();
        private List<int> _invalidateIdList = new List<int>();

        private string _keyProperty = "";
        private Dictionary<string, string> _modelDefineDic = new Dictionary<string, string>();

        private const string Submit_Success = "提交成功";
        private const string ExcelFileFilter = "Excel Files(*.xlsx;*.xls)|*.xlsx;*.xls";
        private const string AppCaption = "Excel导入工具 V1.0(20171129)";

        /// <summary>
        /// 导入相关属性
        /// </summary>
        private readonly IEnumerable<System.Reflection.PropertyInfo> _importProperty;
        private readonly List<string> _importPropertyDescList;

        /// <summary>
        /// 空值
        /// </summary>
        private class EmptyAttribute : Attribute
        {

        }

        /// <summary>
        /// 关键属性
        /// </summary>
        private class KeyAttribute : Attribute
        {

        }

        private class DataModel
        {
            [Description("提交状态"), Empty, ReadOnly(true)]
            public string SubmitStatus { get; set; }

            [Description("系统自编号"), ReadOnly(true), Key]
            public int Id { get; set; }

            [Description("货品编号")]
            public string GoodsSn { get; set; }

            [Description("货品名称")]
            public string GoodsName { get; set; }

            [Description("颜色")]
            public string Color { get; set; }

            [Description("Sku")]
            public string Sku { get; set; }

            [Description("尺码")]
            public string Size { get; set; }

            [Description("成分含量")]
            public string ComponentContent { get; set; }

            [Description("等级")]
            public string Grade { get; set; }

            [Description("检验员")]
            public string Inspection { get; set; }

            [Description("执行标准")]
            public string ImplementationOfStandards { get; set; }

            [Description("单价")]
            public string MarketPrice { get; set; }

            [Description("产家")]
            public string Producer { get; set; }

            [Description("产地"), ReadOnly(true)]
            public string Origin { get; set; }

            [Description("地址"), ReadOnly(true)]
            public string Address { get; set; }

            [Description("操作员"), ReadOnly(true)]
            public string Operator { get; set; }

            [Description("操作时间"), ReadOnly(true)]
            public DateTime? OperationTime { get; set; }

            [Description("审核者"), ReadOnly(true)]
            public string Auditor { get; set; }

            [Description("审核时间"), ReadOnly(true)]
            public DateTime? AuditTime { get; set; }
        }

        private class BasicInfoModel
        {
            public string Category { get; set; }

            public int Id { get; set; }

            public string Name { get; set; }
        }

        public MainForm()
        {
            InitializeComponent();
            Text = AppCaption;
            var properties = typeof(DataModel).GetProperties();
            foreach (var pi in properties)
            {
                var desc_attr = pi.GetCustomAttributes(typeof(DescriptionAttribute), false).FirstOrDefault() as DescriptionAttribute;
                if (desc_attr != null)
                {
                    //if (!pi.GetCustomAttributes(typeof(EmptyAttribute), false).Any())
                    _modelDefineDic[desc_attr.Description] = pi.Name;
                    if (pi.GetCustomAttributes(typeof(KeyAttribute), false).Any())
                    {
                        _keyProperty = desc_attr.Description;
                    }
                }
            }
            _importProperty = properties.Where(
                       pi =>
                               !pi.GetCustomAttributes(typeof(ReadOnlyAttribute), false).Any() &&
                               !pi.GetCustomAttributes(typeof(EmptyAttribute), false).Any() &&
                               !pi.GetCustomAttributes(typeof(KeyAttribute), false).Any()
                  );
            _importPropertyDescList=_importProperty.Select(s =>
                  {
                      var desc_attr = s.GetCustomAttributes(typeof(DescriptionAttribute), false).FirstOrDefault() as DescriptionAttribute;
                      if (desc_attr != null)
                          return desc_attr.Description;
                      return "";
                  }).ToList();
        }


        private static object SD_ChanageType(object value, Type convertsionType)
        {
            //判断convertsionType类型是否为泛型，因为nullable是泛型类,
            if (convertsionType.IsGenericType &&
                //判断convertsionType是否为nullable泛型类
                convertsionType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
            {
                if (value == null || value.ToString().Length == 0)
                {
                    return null;
                }
                //如果convertsionType为nullable类，声明一个NullableConverter类，该类提供从Nullable类到基础基元类型的转换
                NullableConverter nullableConverter = new NullableConverter(convertsionType);
                //将convertsionType转换为nullable对的基础基元类型
                convertsionType = nullableConverter.UnderlyingType;
            }
            return Convert.ChangeType(value, convertsionType);
        }

        private DataModel DataGridViewRowToModel(DataGridViewRow row)
        {
            var model = new DataModel();
            foreach (DataGridViewColumn col in row.DataGridView.Columns)
            {
                if (_modelDefineDic.ContainsKey(col.Name))
                {
                    var pi_name = _modelDefineDic[col.Name];
                    var pi_value = row.Cells[col.Name].Value;
                    var pi = model.GetType().GetProperty(pi_name);
                    if (pi != null)
                    {
                        var value = pi_value;
                        if (value == DBNull.Value)
                            value = null;
                        if (value != null)
                        {
                            if (pi.PropertyType == typeof(bool) || pi.PropertyType == typeof(bool?))
                            {
                                value = Convert.ToBoolean(value);
                            }
                            else if (pi.PropertyType == typeof(int) || pi.PropertyType == typeof(int?))
                            {
                                value = Convert.ToInt32(value);
                            }
                            else if (pi.PropertyType == typeof(long) || pi.PropertyType == typeof(long?))
                            {
                                value = Convert.ToInt64(value);
                            }
                        }
                        if (pi.PropertyType != typeof(string) && value != null)
                            value = SD_ChanageType(value, pi.PropertyType);

                        pi.SetValue(model, value, null);
                    }
                }
            }
            return model;
        }

        private List<DataModel> GetModifyModelList(DataGridView dgv)
        {
            List<DataModel> output = new List<DataModel>();
            if (dgv.Columns.Contains(_keyProperty))
            {
                foreach (DataGridViewRow row in dgv.Rows)
                {
                    var model = DataGridViewRowToModel(row);
                    if (_updateIdList.IndexOf(model.Id) != -1)
                    {
                        if (model.SubmitStatus != Submit_Success)
                            output.Add(model);
                    }
                }
            }
            return output;
        }

        private List<DataModel> GetInsertModelList(DataGridView dgv)
        {
            List<DataModel> output = new List<DataModel>();
            foreach (DataGridViewRow row in dgv.Rows)
            {
                var model = DataGridViewRowToModel(row);
                if (model.SubmitStatus != Submit_Success)
                    output.Add(model);
            }
            return output;
        }

        private List<BasicInfoModel> GetBasicInfoModelList(string category, string tabName)
        {
            var output = new List<BasicInfoModel>();
            var helper = new SQLServerHelper();
            output.AddRange(helper.Query<BasicInfoModel>(string.Format("select '{0}' as Category,Id,Name from {1} order by Id", category, tabName)));
            return output;
        }

        public void BindDBData(System.Windows.Forms.DataGridView dgv, DataTable dt, System.Windows.Forms.ToolStripLabel lb)
        {
            lb.Text = "记录数：0";

            CommonClass.UpdateGetDataStatus(dgv);
            dgv.DataSource = dt;
            lb.Text = "记录数：" + dt.Rows.Count;
        }


        private void button1_Click(object sender, EventArgs e)
        {
            if (_data == null)
            {
                MessageBox.Show("请先获取数据");
                return;
            }
            var keyword = textBox1.Text;
            var dtClone = _data.Clone();
            dtClone.Rows.Clear();
            DataRow[] rows = null;
            if (checkBox1.Checked)
            {
                if (_updateIdList.Count == 0)
                {
                    MessageBox.Show("没有待提交的数据");
                    return;
                }
                var idSql = string.Join(",", _updateIdList.ConvertAll(s => s.ToString()).ToArray());
                rows = _data.Select(string.Format("系统自编号 in ({0}) ", idSql), "操作时间 desc");
            }
            else
                rows = _data.Select(string.Format("货品编号 like '%{0}%' or Sku='{0}' or 货品名称 like '%{0}%' ", keyword), "操作时间 desc");
            foreach (DataRow row in rows)
            {
                var newrow = dtClone.NewRow();
                for (var i = 0; i < _data.Columns.Count; ++i)
                {
                    newrow[i] = row[i];
                }
                dtClone.Rows.Add(newrow);
            }
            BindDBData(dataGridView1, dtClone, toolStripStatusLabel1);
        }

        private void dataGridView1_CellValueChanged(object sender, DataGridViewCellEventArgs e)
        {
            if (_updateIdList.IndexOf(e.RowIndex) == -1)
                if (dataGridView1.Columns.Contains(_keyProperty) && !string.IsNullOrEmpty(_keyProperty))
                {
                    _updateIdList.Add(Convert.ToInt32(dataGridView1.Rows[e.RowIndex].Cells[_keyProperty].Value));
                }
        }

        private void dataGridView1_CellPainting(object sender, DataGridViewCellPaintingEventArgs e)
        {
            if (e.RowIndex >= 0)
            {
                if (dataGridView1.Columns.Contains(_keyProperty) && !string.IsNullOrEmpty(_keyProperty))
                {
                    var id = Convert.ToInt32(dataGridView1.Rows[e.RowIndex].Cells[_keyProperty].Value);
                    if (_updateIdList.IndexOf(id) != -1)
                    {
                        var rawf = e.CellStyle.Font;
                        e.CellStyle.Font = new Font(rawf, FontStyle.Bold);
                        if (_invalidateIdList.IndexOf(id) == -1)
                        {
                            _invalidateIdList.Add(id);
                            dataGridView1.InvalidateRow(e.RowIndex);
                        }
                    }
                }
            }
        }

        private void 获取数据ToolStripMenuItem_Click_1(object sender, EventArgs e)
        {
            List<bool> readonlyColumnList = new List<bool>();
            string sql = @"SELECT ";
            var properties = typeof(DataModel).GetProperties();
            foreach (var pi in properties)
            {
                var desc_attr = pi.GetCustomAttributes(typeof(DescriptionAttribute), false).FirstOrDefault() as DescriptionAttribute;
                if (desc_attr != null)
                {
                    var fieldName = "";
                    var aliasName = "";

                    aliasName = desc_attr.Description;
                    if (!pi.GetCustomAttributes(typeof(EmptyAttribute), false).Any())
                        fieldName = pi.Name;
                    else
                        fieldName = "''";
                    sql += string.Format("{0} as '{1}',", fieldName, aliasName);
                    readonlyColumnList.Add(pi.GetCustomAttributes(typeof(ReadOnlyAttribute), false).Any());
                }

            }
            sql = sql.TrimEnd(',');
            sql += " FROM [CommodityLabelInfoView2] Order By OperationTime desc";
            var helper = new SQLServerHelper();
            _data = helper.GetDataTable(sql);
            BindDBData(dataGridView1, _data, toolStripStatusLabel1);
            for (int i = 0; i < readonlyColumnList.Count; i++)
            {
                dataGridView1.Columns[i].ReadOnly = readonlyColumnList[i];
            }
            _updateIdList.Clear();
            _invalidateIdList.Clear();
            panel1.Visible = true;
        }

        private DataTable ToDataTable(string path, bool isFirstRowColumn)
        {
            ISheet sheet = null;
            DataTable data = new DataTable();
            int startRow = 0;
            IWorkbook m_workbook = null;
            using (var fs = new FileStream(path, FileMode.Open))
            {
                try
                {
                    var ext = System.IO.Path.GetExtension(path).ToLower();
                    if (ext == ".xlsx") // 2007版本
                        m_workbook = new XSSFWorkbook(fs);
                    else if (ext == ".xls") // 2003版本
                        m_workbook = new HSSFWorkbook(fs);
                    else
                        throw new Exception("只允许上传xlsx、xls格式的文件");

                    sheet = m_workbook.GetSheetAt(0);
                    if (sheet != null)
                    {

                        IRow firstRow = sheet.GetRow(0);
                        int cellCount = firstRow.LastCellNum;

                        if (isFirstRowColumn)
                        {
                            for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                            {
                                DataColumn column = new DataColumn(firstRow.GetCell(i).ToString());
                                data.Columns.Add(column);
                            }
                            startRow = sheet.FirstRowNum + 1;
                        }
                        else
                        {
                            startRow = sheet.FirstRowNum;
                        }



                        //最后一列的标号
                        int rowCount = sheet.LastRowNum;
                        for (int i = startRow; i <= rowCount; ++i)
                        {
                            IRow row = sheet.GetRow(i);
                            if (row == null) continue; //没有数据的行默认是null　　　　　　　

                            DataRow dataRow = data.NewRow();
                            for (int j = row.FirstCellNum; j < cellCount; ++j)
                            {
                                if (j >= 0)
                                {
                                    if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
                                    {
                                        var cell = row.GetCell(j);
                                        string text = cell.ToString();
                                        if (cell.CellType == CellType.Numeric && DateUtil.IsCellDateFormatted(cell))
                                            text = cell.DateCellValue.ToString("yyyy-MM-dd");
                                        if (text.IndexOf("%") != -1)
                                        {
                                            if (text[0] == '.')
                                                text = "0" + text;
                                        }
                                        dataRow[j] = text;
                                    }
                                }
                            }
                            data.Rows.Add(dataRow);
                        }
                    }

                    return data;
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    return null;
                }
                finally
                {
                    if (m_workbook != null)
                        m_workbook.Close();
                }
            }
        }

        /// <summary>
        /// 将DataTable数据导入到excel中
        /// </summary>
        /// <param name="data">要导入的数据</param>
        /// <param name="isColumnWritten">DataTable的列名是否要导入</param>
        /// <param name="sheetName">要导入的excel的sheet的名称</param>
        /// <returns>导入数据行数(包含列名那一行)</returns>
        public int DataTableToExcel(string path, DataTable data, string sheetName, bool isColumnWritten)
        {
            int i = 0;
            int j = 0;
            int count = 0;
            ISheet sheet = null;
            IWorkbook m_workbook = null;

            using (var m_fs = new FileStream(path, FileMode.OpenOrCreate, FileAccess.ReadWrite))
            {
                var ext = System.IO.Path.GetExtension(path).ToLower();
                if (ext == ".xlsx") // 2007版本
                    m_workbook = new XSSFWorkbook();
                else if (ext == ".xls") // 2003版本
                    m_workbook = new HSSFWorkbook();

                try
                {
                    if (m_workbook != null)
                    {
                        sheet = m_workbook.CreateSheet(sheetName);
                    }
                    else
                    {
                        return -1;
                    }

                    ICellStyle style = m_workbook.CreateCellStyle();
                    //style.Alignment = HorizontalAlignment.Center;
                    //style.WrapText = true;
                    IFont font = m_workbook.CreateFont();
                    //font.FontHeightInPoints = 16;
                    font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
                    font.FontName = "宋体";
                    style.SetFont(font);//HEAD 样式

                    if (isColumnWritten == true) //写入DataTable的列名
                    {
                        IRow row = sheet.CreateRow(0);
                        for (j = 0; j < data.Columns.Count; ++j)
                        {
                            var cell = row.CreateCell(j);
                            cell.CellStyle = style;
                            cell.SetCellValue(data.Columns[j].ColumnName);
                        }
                        count = 1;
                    }
                    else
                    {
                        count = 0;
                    }

                    for (i = 0; i < data.Rows.Count; ++i)
                    {
                        IRow row = sheet.CreateRow(count);
                        for (j = 0; j < data.Columns.Count; ++j)
                        {
                            string v = data.Rows[i][j].ToString();
                            double o = 0;
                            if (double.TryParse(v, out o))
                            {
                                row.CreateCell(j).SetCellValue(o);
                            }
                            else
                            {
                                if (v.Length > 0)
                                    if (v[0] == '\'')
                                        v = v.Substring(1);
                                row.CreateCell(j).SetCellValue(v);
                            }
                        }
                        ++count;
                    }

                    for (var x = 0; x < data.Columns.Count; ++x)
                        sheet.AutoSizeColumn(x);

                    m_workbook.Write(m_fs); //写入到excel
                    return count;
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Exception: " + ex.Message);
                    return -1;
                }
                finally
                {
                    if (m_workbook != null)
                        m_workbook.Close();
                }
            }

        }

        private void 导入数据ToolStripMenuItem_Click_1(object sender, EventArgs e)
        {
            using (var dlg = new OpenFileDialog())
            {
                dlg.Filter = ExcelFileFilter;
                if (dlg.ShowDialog() == DialogResult.OK)
                {
                    try
                    {
                        var dt = ToDataTable(dlg.FileName, true);
                        if (dt.Columns.Count != _importPropertyDescList.Count)
                            throw new Exception("导入Excel列数与模板列数不一致");
                        
                        foreach(DataColumn col in dt.Columns)
                        {
                            if (_importPropertyDescList.IndexOf(col.ColumnName) == -1)
                                throw new Exception("列名\"" + col.ColumnName + "\"不在模板列范围内");
                        }

                        var real_dt = new DataTable();
                        var desc = GetSubmitStatusDescription();
                        if (!string.IsNullOrEmpty(desc))
                            real_dt.Columns.Add(desc);
                        foreach (DataColumn col in dt.Columns)
                            real_dt.Columns.Add(col.ColumnName, col.DataType);

                        foreach(DataRow row in dt.Rows)
                        {
                            var newrow = real_dt.NewRow();
                            foreach(DataColumn col in dt.Columns)
                            {
                                newrow[col.ColumnName] = row[col.ColumnName];
                            }
                            real_dt.Rows.Add(newrow);
                        }
                        BindDBData(dataGridView1, real_dt, toolStripStatusLabel1);
                        panel1.Visible = false;
                        for (int i = 0; i < dataGridView1.ColumnCount; i++)
                        {
                            dataGridView1.Columns[i].ReadOnly = true;
                        }
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }
                }
            }
        }

        private void 提交修改ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            var allBasicInfoModelList = new List<BasicInfoModel>();
            var tabDic = new Dictionary<string, string>();
            tabDic.Add("B_Color", "颜色");
            tabDic.Add("B_Grade", "等级");
            tabDic.Add("B_ImplementationOfStandards", "执行标准");
            tabDic.Add("B_Inspection", "检验员");
            tabDic.Add("B_Producer", "产家");
            foreach (var kv in tabDic)
            {
                allBasicInfoModelList.AddRange(GetBasicInfoModelList(kv.Value, kv.Key));
            }

            //只显示待提交状态数据
            if (!checkBox1.Checked)
                checkBox1.Checked = true;

            var modelList = GetModifyModelList(dataGridView1);
            modelList.ForEach(model =>
            {
                model.OperationTime = DateTime.Now;
                var sql = "update CommodityLabelInfo set ";
                foreach (var pi in model.GetType().GetProperties())
                {
                    var pi_value = pi.GetValue(model, null);
                    switch (pi.Name)
                    {
                        case "Color":
                            {
                                var colorName = Convert.ToString(pi_value);
                                var key = "B_" + pi.Name;
                                if (tabDic.ContainsKey(key))
                                {
                                    var value = tabDic[key];
                                    var findItem = allBasicInfoModelList.Find(s => s.Category == value && s.Name == colorName);
                                    if (findItem == null)
                                    {
                                        model.SubmitStatus = string.Format("提交失败,未在\"{0}\"找到\"{1}\"项,请在基础档案中添加", value, colorName);
                                        return;
                                    }
                                    sql += pi.Name + "Id=" + findItem.Id + ",";
                                }
                            }
                            break;
                        case "Grade":
                        case "ImplementationOfStandards":
                        case "Inspection":
                        case "Producer":
                            {
                                var name = Convert.ToString(pi_value);
                                var key = "B_" + pi.Name;
                                if (tabDic.ContainsKey(key))
                                {
                                    var value = tabDic[key];
                                    var findItem = allBasicInfoModelList.Find(s => s.Category == value && s.Name == name);
                                    if (findItem == null)
                                    {
                                        model.SubmitStatus = string.Format("提交失败,未在\"{0}\"找到\"{1}\"项,请在基础档案中添加", value, name);
                                        return;
                                    }
                                    sql += pi.Name + "=" + findItem.Id + ",";
                                }
                            }
                            break;
                        default:
                            {
                                if (!pi.GetCustomAttributes(typeof(EmptyAttribute), false).Any() &&
                                    !pi.GetCustomAttributes(typeof(ReadOnlyAttribute), false).Any() &&
                                    !pi.GetCustomAttributes(typeof(KeyAttribute), false).Any()
                                  )
                                {
                                    var value = pi_value;
                                    if (value == DBNull.Value)
                                        value = null;
                                    sql += pi.Name + "='" + Convert.ToString(value) + "',";
                                }
                            }
                            break;
                    }
                }
                sql = sql.TrimEnd(',');
                sql += " where Id=" + model.Id;

                try
                {
                    var helper = new SQLServerHelper();
                    helper.ExecCmd(sql);
                    model.SubmitStatus = Submit_Success;
                }
                catch (Exception ex)
                {
                    model.SubmitStatus = "提交失败," + ex.Message;
                }

            });

            UpdateSubmitStatus(dataGridView1, modelList);
        }

        private string GetSubmitStatusDescription()
        {
            var output = "";
            do
            {
                var pi = typeof(DataModel).GetProperty("SubmitStatus");
                if (pi == null)
                    break;

                var desc_attr = pi.GetCustomAttributes(typeof(DescriptionAttribute), false).FirstOrDefault() as DescriptionAttribute;
                if (desc_attr == null)
                    break;

                output = desc_attr.Description;
            } while (false);

            return output;
        }

        private void UpdateSubmitStatus(DataGridView dgv, List<DataModel> modelList)
        {
            foreach (DataGridViewRow row in dgv.Rows)
            {
                var id = Convert.ToInt32(row.Cells[_keyProperty].Value);
                var findItem = modelList.Find(s => s.Id == id);
                if (findItem != null)
                {
                    var desc = GetSubmitStatusDescription();
                    if (!string.IsNullOrEmpty(desc))
                        row.Cells[desc].Value = findItem.SubmitStatus;
                }
            }
        }

        private void MainForm_Load(object sender, EventArgs e)
        {
            //单元格多行显示
            dataGridView1.DefaultCellStyle.WrapMode = DataGridViewTriState.True;
            panel1.Visible = false;
        }

        private void checkBox1_CheckedChanged(object sender, EventArgs e)
        {
            var state = !checkBox1.Checked;
            textBox1.Enabled = state;
            button1.Enabled = state;
            button1_Click(null, null);
        }

        private void 下载导入模板ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            using (var dlg = new SaveFileDialog())
            {
                dlg.Filter = ExcelFileFilter;
                dlg.FileName = "导入模板.xlsx";
                if (dlg.ShowDialog() == DialogResult.OK)
                {
                    var dt = new DataTable();
                    foreach (var p in _importPropertyDescList)
                    {
                         dt.Columns.Add(p, typeof(string));
                    }
                    if (DataTableToExcel(dlg.FileName, dt, "导入数据", true) != -1)
                    {
                        MessageBox.Show("下载成功");
                    }
                }
            }
        }

        private void 提交导入ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            var allBasicInfoModelList = new List<BasicInfoModel>();
            var tabDic = new Dictionary<string, string>();
            tabDic.Add("B_Color", "颜色");
            tabDic.Add("B_Grade", "等级");
            tabDic.Add("B_ImplementationOfStandards", "执行标准");
            tabDic.Add("B_Inspection", "检验员");
            tabDic.Add("B_Producer", "产家");
            foreach (var kv in tabDic)
            {
                allBasicInfoModelList.AddRange(GetBasicInfoModelList(kv.Value, kv.Key));
            }

            var modelList = GetInsertModelList(dataGridView1);
            modelList.ForEach(model =>
            {
                model.SubmitStatus = "";
                var sql = "insert into CommodityLabelInfo(";
                var valueSql = "values(";
                _importPropertyDescList.ForEach(desc =>
                {
                    var pi = _importProperty.Where(s =>
                      {
                          var desc_attr = s.GetCustomAttributes(typeof(DescriptionAttribute), false).FirstOrDefault() as DescriptionAttribute;
                          if (desc_attr != null)
                          {
                              return desc_attr.Description == desc;
                          }
                          return false;
                      }).FirstOrDefault();
                    if (pi != null)
                    {
                        sql += pi.Name + ",";
                        var pi_value = pi.GetValue(model, null);
                        switch (pi.Name)
                        {
                            case "Color":
                                {
                                    var colorName = Convert.ToString(pi_value);
                                    var key = "B_" + pi.Name;
                                    if (tabDic.ContainsKey(key))
                                    {
                                        var value = tabDic[key];
                                        var findItem = allBasicInfoModelList.Find(s => s.Category == value && s.Name == colorName);
                                        if (findItem == null)
                                        {
                                            model.SubmitStatus = string.Format("提交失败,未在\"{0}\"找到\"{1}\"项,请在基础档案中添加", value, colorName);
                                            return;
                                        }
                                        valueSql += findItem.Id + ",";
                                    }
                                }
                                break;
                            case "Grade":
                            case "ImplementationOfStandards":
                            case "Inspection":
                            case "Producer":
                                {
                                    var name = Convert.ToString(pi_value);
                                    var key = "B_" + pi.Name;
                                    if (tabDic.ContainsKey(key))
                                    {
                                        var value = tabDic[key];
                                        var findItem = allBasicInfoModelList.Find(s => s.Category == value && s.Name == name);
                                        if (findItem == null)
                                        {
                                            model.SubmitStatus = string.Format("提交失败,未在\"{0}\"找到\"{1}\"项,请在基础档案中添加", value, name);
                                            return;
                                        }
                                        valueSql += findItem.Id + ",";
                                    }
                                }
                                break;
                            default:
                                {
                                    if (!pi.GetCustomAttributes(typeof(EmptyAttribute), false).Any() &&
                                        !pi.GetCustomAttributes(typeof(ReadOnlyAttribute), false).Any() &&
                                        !pi.GetCustomAttributes(typeof(KeyAttribute), false).Any()
                                      )
                                    {
                                        var value = pi_value;
                                        if (value == DBNull.Value)
                                            value = null;
                                        valueSql += "'" + Convert.ToString(value) + "',";
                                    }
                                }
                                break;
                        }
                    }
                });
                sql = sql.TrimEnd(',') + ") " + valueSql.TrimEnd(',') + ")";
                
                //非空表示出错了
                if (!string.IsNullOrEmpty(model.SubmitStatus))
                    return;

                try
                {
                    var helper = new SQLServerHelper();
                    helper.ExecCmd(sql);
                    model.SubmitStatus = Submit_Success;
                }
                catch (Exception ex)
                {
                    model.SubmitStatus = "提交失败," + ex.Message;
                }
            });
            //更新提交状态
            for(var i = 0; i < modelList.Count; ++i)
            {
                var desc = GetSubmitStatusDescription();
                if (dataGridView1.Columns.Contains(desc))
                    dataGridView1.Rows[i].Cells[desc].Value = modelList[i].SubmitStatus;
            }
        }

        private void 关于本程序ToolStripMenuItem_Click(object sender, EventArgs e)
        {
            MessageBox.Show(Text + "\r\n\r\n开发者：夜风\r\nE-mail：752923276@qq.com", "关于本程序", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
    }
}
